package com.example.edu_realtime_ads.mapper;

import com.example.edu_realtime_ads.bean.CategoryWithOrder;
import com.example.edu_realtime_ads.bean.CourseWithOrder;
import com.example.edu_realtime_ads.bean.ReviewCourse;
import com.example.edu_realtime_ads.bean.SubjectwithOrder;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;


import java.util.List;

/**
 * @author MengX
 * @create 2023/3/28 11:02:35
 */
public interface OrderMapper {
    @Select("select \n" +
            "    subject_id,\n" +
            "    subject_name,\n" +
            "    sum(order_userct) order_userct,\n" +
            "    sum(order_count) order_count,\n" +
            "    sum(order_amount) order_amount\n" +
            "from dws_cource_order_detail_subject_window\n" +
            "where toYYYYMMDD(stt) = ${date}\n" +
            "group by subject_id,subject_name")
    List<SubjectwithOrder> getSubjectWithOrderTable(@Param("date") Integer date);
    @Select("select \n" +
            "    category_id,\n" +
            "    sum(order_userct) order_userct,\n" +
            "    sum(order_count) order_count,\n" +
            "    sum(order_amount) order_amount\n" +
            "from dws_trade_order_count_window\n" +
            "where toYYYYMMDD(stt) = ${date}\n" +
            "group by category_id\n")
    List<CategoryWithOrder> getCategoryWithOrderTable(@Param("date") Integer date);
    @Select("select \n" +
            "    course_id,\n" +
            "    course_name,\n" +
            "    sum(order_userct) order_userct,\n" +
            "    cast(sum(order_amount) as Decimal32(2)) AS order_amount\n" +
            "from dws_cource_order_detail_window\n" +
            "where toYYYYMMDD(stt) = ${date}\n" +
            "group by course_id,course_name")
    List<CourseWithOrder> getCourseJson(@Param("date") Integer date);
    @Select("\n" +
            "select \n" +
            "    course_id,\n" +
            "    course_name,\n" +
            "    avg(user_star_avg) user_star_avg,\n" +
            "    sum(review_userct) review_userct,\n" +
            "    avg(positive_review) positive_review\n" +
            "from dws_user_review_info_window\n" +
            "where toYYYYMMDD(stt) = ${date}\n" +
            "group by course_id,course_name")
    List<ReviewCourse> getReview(@Param("date") Integer date);
}
